SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_PublicSearchLecture]
    (
      @JOL_Field_ApplyCompany VARCHAR(50) ,
      @JOL_Lecture_Title VARCHAR(50)
    )
AS 
    SELECT  a.JOL_Lecture_No ,
            c.JOL_Employer_Name ,
            a.JOL_Lecture_Title ,
            b.JOL_Field_ApplyTime ,
            b.JOL_Field_ApplyPosition ,
            b.JOL_Field_Remark
--            a.JOL_Lecture_Linkman ,
--            a.JOL_Lecture_Mobile ,
--            a.JOL_Lecture_Email ,
--            a.JOL_Lecture_Fax ,
--            a.JOL_Lecture_VerifyStatus
            --c.JOL_Employer_Email
    FROM    dbo.JOL_Lecture AS a
            INNER JOIN dbo.JOL_ApplyField AS b ON a.JOL_Lecture_Purpose = b.JOL_Field_No
            INNER JOIN dbo.JOL_Employer AS c ON b.JOL_Field_ApplyCompany = c.JOL_Employer_Account
    WHERE   b.JOL_Field_Purpose = '宣讲会'
            AND ( c.JOL_Employer_Name LIKE '%' + @JOL_Field_ApplyCompany + '%' )
            AND ( a.JOL_Lecture_Title LIKE '%' + @JOL_Lecture_Title + '%' )
            AND a.JOL_Lecture_VerifyStatus = '已审核'
    ORDER BY b.JOL_Field_ApplyTime DESC ,
            b.JOL_Field_ApplyPosition DESC
     --a.JOL_Lecture_VerifyStatus DESC,
     --       a.JOL_Lecture_IsSetTop DESC,
     --       a.JOL_Lecture_AddTime DESC,
     --       a.JOL_Lecture_UpdateTime DESC,
     --       b.JOL_Field_ApplyCompany
GO
